"""
扩展告警API模块
包含批量操作、热点分析、报告生成等高级功能
"""

from flask import Blueprint, request, jsonify, current_app
from datetime import datetime, timedelta
import json
from app.utils.mysql_db import get_db_connection, get_db_cursor, DatabaseLogger
from app.utils.logger import FlinkLogger, log_api_call, log_database_operation


bp = Blueprint("alerts_extended", __name__)


@bp.post("/batch")
@log_api_call("alerts_extended.batch_alert_operations")
def batch_alert_operations():
    """批量告警操作"""
    current_app.logger.info("Starting batch alert operations")
    data = request.get_json()
    if not data:
        return {"success": False, "error": "Request body is required", "code": "BAD_REQUEST"}, 400
    
    alert_ids = data.get("alert_ids", [])
    action = data.get("action")  # resolve, silence, escalate, delete
    
    if not alert_ids:
        return {"success": False, "error": "alert_ids is required", "code": "BAD_REQUEST"}, 400
    
    if not action:
        return {"success": False, "error": "action is required", "code": "BAD_REQUEST"}, 400
    
    if action not in ["resolve", "silence", "escalate", "delete"]:
        return {"success": False, "error": "Invalid action", "code": "BAD_REQUEST"}, 400
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            success_count = 0
            failed_count = 0
            results = []
            
            for alert_id in alert_ids:
                try:
                    # 检查告警是否存在
                    cur.execute("SELECT id, status, job_name FROM flink_cluster_alert_history WHERE id = %s", (alert_id,))
                    alert = cur.fetchone()
                    
                    if not alert:
                        results.append({"alert_id": alert_id, "status": "failed", "reason": "Alert not found"})
                        failed_count += 1
                        continue
                    
                    if action == "resolve":
                        reason = data.get("resolution_reason", "批量解决")
                        operator = data.get("resolved_by", "系统用户")
                        
                        if alert['status'] == 'RESOLVED':
                            results.append({"alert_id": alert_id, "status": "skipped", "reason": "Already resolved"})
                            continue
                        
                        cur.execute("""
                            UPDATE flink_cluster_alert_history
                            SET status = 'RESOLVED', 
                                resolved_time = NOW(),
                                escalation_reason = %s
                            WHERE id = %s
                        """, (f"批量解决: {reason} (操作人: {operator})", alert_id))
                        
                    elif action == "silence":
                        minutes = int(data.get("minutes", 60))
                        reason = data.get("silence_reason", "批量静默")
                        operator = data.get("silenced_by", "系统用户")
                        
                        silence_until = datetime.now() + timedelta(minutes=minutes)
                        
                        cur.execute("""
                            UPDATE flink_cluster_alert_history
                            SET status = 'SILENCED', 
                                silence_until = %s,
                                escalation_reason = %s
                            WHERE id = %s
                        """, (
                            silence_until.strftime('%Y-%m-%d %H:%M:%S'),
                            f"批量静默: {reason} (操作人: {operator}, 时长: {minutes}分钟)",
                            alert_id
                        ))
                        
                    elif action == "escalate":
                        reason = data.get("escalation_reason", "批量升级")
                        
                        cur.execute("""
                            UPDATE flink_cluster_alert_history
                            SET status = 'ESCALATED',
                                escalation_reason = %s
                            WHERE id = %s
                        """, (f"批量升级: {reason}", alert_id))
                        
                    elif action == "delete":
                        cur.execute("DELETE FROM flink_cluster_alert_history WHERE id = %s", (alert_id,))
                    
                    results.append({
                        "alert_id": alert_id, 
                        "status": "success", 
                        "job_name": alert['job_name']
                    })
                    success_count += 1
                    
                except Exception as e:
                    results.append({"alert_id": alert_id, "status": "failed", "reason": str(e)})
                    failed_count += 1
            
            conn.commit()
            
            return {
                "message": f"Batch operation completed",
                "action": action,
                "total": len(alert_ids),
                "success": success_count,
                "failed": failed_count,
                "results": results
            }
            
    except Exception as e:
        conn.rollback()
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.get("/hotspots")
@log_api_call("alerts_extended.get_alert_hotspots")
def get_alert_hotspots():
    """获取告警热点分析"""
    current_app.logger.info("Getting alert hotspots analysis")
    days = request.args.get("days", 7, type=int)
    limit = request.args.get("limit", 10, type=int)
    alert_types_param = request.args.get("alert_types", "")  # 逗号分隔的告警类型列表
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            # 告警类型过滤条件
            alert_type_filter = ""
            if alert_types_param:
                # 解析逗号分隔的告警类型，并构建IN子句
                types_list = [t.strip() for t in alert_types_param.split(',') if t.strip()]
                if types_list:
                    types_str = "', '".join(types_list)
                    alert_type_filter = f"AND alert_type IN ('{types_str}')"
            
            # 作业告警热点
            cur.execute(f"""
                SELECT 
                    job_name,
                    COUNT(*) as alert_count,
                    SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) as active_count,
                    SUM(CASE WHEN alert_type = 'FINAL_FAILURE' THEN 1 ELSE 0 END) as final_failure_count,
                    AVG(CASE WHEN resolved_time IS NOT NULL 
                        THEN TIMESTAMPDIFF(MINUTE, sent_time, resolved_time) 
                        ELSE NULL END) as avg_resolution_minutes,
                    MAX(sent_time) as last_alert_time
                FROM flink_cluster_alert_history 
                WHERE sent_time >= DATE_SUB(NOW(), INTERVAL %s DAY)
                    AND job_name != 'cluster'
                    {alert_type_filter}
                GROUP BY job_name
                ORDER BY alert_count DESC
                LIMIT %s
            """, (days, limit))
            
            job_hotspots = cur.fetchall()
            
            # 告警类型热点
            cur.execute(f"""
                SELECT 
                    alert_type,
                    COUNT(*) as count,
                    COUNT(DISTINCT job_name) as affected_jobs
                FROM flink_cluster_alert_history 
                WHERE sent_time >= DATE_SUB(NOW(), INTERVAL %s DAY)
                    {alert_type_filter}
                GROUP BY alert_type
                ORDER BY count DESC
            """, (days,))
            
            type_hotspots = cur.fetchall()
            
            # 时间热点（小时维度）
            cur.execute(f"""
                SELECT 
                    HOUR(sent_time) as hour,
                    COUNT(*) as count
                FROM flink_cluster_alert_history 
                WHERE sent_time >= DATE_SUB(NOW(), INTERVAL %s DAY)
                    {alert_type_filter}
                GROUP BY HOUR(sent_time)
                ORDER BY count DESC
            """, (days,))
            
            time_hotspots = cur.fetchall()
            
            # 格式化时间字段
            for job in job_hotspots:
                if job.get('last_alert_time'):
                    job['last_alert_time'] = job['last_alert_time'].strftime('%Y-%m-%d %H:%M:%S')
                if job.get('avg_resolution_minutes'):
                    job['avg_resolution_hours'] = round(job['avg_resolution_minutes'] / 60, 2)
            
            return {
                "job_hotspots": job_hotspots,
                "type_hotspots": type_hotspots,
                "time_hotspots": time_hotspots,
                "days": days
            }
            
    except Exception as e:
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.get("/config")
@log_api_call("alerts_extended.get_alert_config")
def get_alert_config():
    """获取告警配置"""
    current_app.logger.info("Getting alert configuration")
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            # 获取告警配置（如果存在配置表）
            config = {
                "alert_cooldown_minutes": 30,
                "max_alerts": 3,
                "escalation_hours": 24,
                "default_silence_minutes": 60,
                "auto_resolve_enabled": True
            }
            
            # 获取告警统计配置信息
            cur.execute("""
                SELECT 
                    COUNT(DISTINCT alert_type) as alert_types_count,
                    COUNT(DISTINCT job_name) as monitored_jobs_count,
                    MIN(sent_time) as oldest_alert,
                    MAX(sent_time) as newest_alert
                FROM flink_cluster_alert_history
            """)
            
            stats = cur.fetchone()
            if stats:
                if stats.get('oldest_alert'):
                    stats['oldest_alert'] = stats['oldest_alert'].strftime('%Y-%m-%d %H:%M:%S')
                if stats.get('newest_alert'):
                    stats['newest_alert'] = stats['newest_alert'].strftime('%Y-%m-%d %H:%M:%S')
                config.update(stats)
            
            return {"config": config}
            
    except Exception as e:
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.post("/config")
@log_api_call("alerts_extended.update_alert_config")
def update_alert_config():
    """更新告警配置"""
    current_app.logger.info("Updating alert configuration")
    data = request.get_json()
    if not data:
        return {"success": False, "error": "Request body is required", "code": "BAD_REQUEST"}, 400
    
    # 这里可以实现配置更新逻辑
    # 目前返回成功响应
    return {
        "message": "Configuration updated successfully",
        "updated_config": data
    }


@bp.get("/reports")
@log_api_call("alerts_extended.generate_alert_report")
def generate_alert_report():
    """生成告警报告"""
    current_app.logger.info("Generating alert report")
    report_type = request.args.get("type", "summary")  # summary, detailed, trend
    start_date = request.args.get("start_date")
    end_date = request.args.get("end_date")
    format_type = request.args.get("format", "json")  # json, csv
    
    if not start_date or not end_date:
        return {"success": False, "error": "start_date and end_date are required", "code": "BAD_REQUEST"}, 400
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            date_filter = "WHERE sent_time BETWEEN %s AND %s"
            
            if report_type == "summary":
                # 摘要报告
                cur.execute(f"""
                    SELECT 
                        COUNT(*) as total_alerts,
                        SUM(CASE WHEN status = 'RESOLVED' THEN 1 ELSE 0 END) as resolved_alerts,
                        SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) as active_alerts,
                        SUM(CASE WHEN status = 'ESCALATED' THEN 1 ELSE 0 END) as escalated_alerts,
                        COUNT(DISTINCT job_name) as affected_jobs,
                        AVG(CASE WHEN resolved_time IS NOT NULL 
                            THEN TIMESTAMPDIFF(MINUTE, sent_time, resolved_time) 
                            ELSE NULL END) as avg_resolution_minutes
                    FROM flink_cluster_alert_history 
                    {date_filter}
                """, (start_date, end_date))
                
                summary = cur.fetchone()
                
                # 按类型统计
                cur.execute(f"""
                    SELECT alert_type, COUNT(*) as count
                    FROM flink_cluster_alert_history 
                    {date_filter}
                    GROUP BY alert_type
                """, (start_date, end_date))
                
                type_breakdown = cur.fetchall()
                
                report_data = {
                    "summary": summary,
                    "type_breakdown": type_breakdown,
                    "period": f"{start_date} to {end_date}"
                }
                
            elif report_type == "detailed":
                # 详细报告
                cur.execute(f"""
                    SELECT 
                        id, job_name, alert_type, alert_title, status,
                        sent_time, resolved_time, send_count
                    FROM flink_cluster_alert_history 
                    {date_filter}
                    ORDER BY sent_time DESC
                """, (start_date, end_date))
                
                alerts = cur.fetchall()
                
                # 格式化时间
                for alert in alerts:
                    if alert.get('sent_time'):
                        alert['sent_time'] = alert['sent_time'].strftime('%Y-%m-%d %H:%M:%S')
                    if alert.get('resolved_time'):
                        alert['resolved_time'] = alert['resolved_time'].strftime('%Y-%m-%d %H:%M:%S')
                
                report_data = {
                    "alerts": alerts,
                    "total": len(alerts),
                    "period": f"{start_date} to {end_date}"
                }
            
            else:  # trend
                # 趋势报告
                cur.execute(f"""
                    SELECT 
                        DATE(sent_time) as date,
                        COUNT(*) as daily_count,
                        SUM(CASE WHEN status = 'RESOLVED' THEN 1 ELSE 0 END) as resolved_count
                    FROM flink_cluster_alert_history 
                    {date_filter}
                    GROUP BY DATE(sent_time)
                    ORDER BY date
                """, (start_date, end_date))
                
                daily_trends = cur.fetchall()
                
                report_data = {
                    "daily_trends": daily_trends,
                    "period": f"{start_date} to {end_date}"
                }
            
            return {
                "report_type": report_type,
                "format": format_type,
                "generated_at": datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                "data": report_data
            }
            
    except Exception as e:
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.get("/recovery-status")
@log_api_call("alerts_extended.get_recovery_status")
def get_recovery_status():
    """获取告警恢复状态"""
    current_app.logger.info("Getting alert recovery status")
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            # 获取可能已恢复但未标记的告警
            cur.execute("""
                SELECT 
                    COUNT(*) as potentially_recovered_alerts,
                    COUNT(DISTINCT job_name) as affected_jobs
                FROM flink_cluster_alert_history 
                WHERE status = 'ACTIVE'
                    AND alert_type IN ('FIRST_FAILURE', 'RETRY_FAILURE', 'FINAL_FAILURE')
                    AND sent_time <= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
            """)
            
            recovery_stats = cur.fetchone()
            
            # 获取长时间未处理的告警
            cur.execute("""
                SELECT 
                    id, job_name, alert_title, sent_time,
                    TIMESTAMPDIFF(HOUR, sent_time, NOW()) as hours_active
                FROM flink_cluster_alert_history 
                WHERE status = 'ACTIVE'
                    AND sent_time <= DATE_SUB(NOW(), INTERVAL 24 HOUR)
                ORDER BY sent_time ASC
                LIMIT 10
            """)
            
            stale_alerts = cur.fetchall()
            
            # 格式化时间
            for alert in stale_alerts:
                if alert.get('sent_time'):
                    alert['sent_time'] = alert['sent_time'].strftime('%Y-%m-%d %H:%M:%S')
            
            return {
                "recovery_stats": recovery_stats,
                "stale_alerts": stale_alerts,
                "recommendation": "建议运行告警恢复检查工具检测已恢复的告警" if recovery_stats['potentially_recovered_alerts'] > 0 else "无需要处理的恢复告警"
            }
            
    except Exception as e:
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.get("/patterns")
@log_api_call("alerts_extended.analyze_alert_patterns")
def analyze_alert_patterns():
    """分析告警模式"""
    current_app.logger.info("Analyzing alert patterns")
    days = request.args.get("days", 30, type=int)
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            # 重复告警模式
            cur.execute("""
                SELECT 
                    job_name,
                    alert_type,
                    COUNT(*) as occurrence_count,
                    AVG(TIMESTAMPDIFF(MINUTE, sent_time, resolved_time)) as avg_resolution_minutes,
                    MIN(sent_time) as first_occurrence,
                    MAX(sent_time) as last_occurrence
                FROM flink_cluster_alert_history 
                WHERE sent_time >= DATE_SUB(NOW(), INTERVAL %s DAY)
                GROUP BY job_name, alert_type
                HAVING COUNT(*) > 1
                ORDER BY occurrence_count DESC
                LIMIT 20
            """, (days,))
            
            recurring_patterns = cur.fetchall()
            
            # 时间模式分析
            cur.execute("""
                SELECT 
                    HOUR(sent_time) as hour,
                    DAYOFWEEK(sent_time) as day_of_week,
                    COUNT(*) as alert_count
                FROM flink_cluster_alert_history 
                WHERE sent_time >= DATE_SUB(NOW(), INTERVAL %s DAY)
                GROUP BY HOUR(sent_time), DAYOFWEEK(sent_time)
                ORDER BY alert_count DESC
                LIMIT 10
            """, (days,))
            
            time_patterns = cur.fetchall()
            
            # 连锁反应分析（相关告警）
            cur.execute("""
                SELECT 
                    a1.job_name as primary_job,
                    a2.job_name as related_job,
                    COUNT(*) as correlation_count
                FROM flink_cluster_alert_history a1
                JOIN flink_cluster_alert_history a2 
                    ON ABS(TIMESTAMPDIFF(MINUTE, a1.sent_time, a2.sent_time)) <= 10
                    AND a1.id != a2.id
                WHERE a1.sent_time >= DATE_SUB(NOW(), INTERVAL %s DAY)
                    AND a1.job_name != a2.job_name
                GROUP BY a1.job_name, a2.job_name
                HAVING COUNT(*) >= 3
                ORDER BY correlation_count DESC
                LIMIT 15
            """, (days,))
            
            correlation_patterns = cur.fetchall()
            
            # 格式化时间字段
            for pattern in recurring_patterns:
                if pattern.get('first_occurrence'):
                    pattern['first_occurrence'] = pattern['first_occurrence'].strftime('%Y-%m-%d %H:%M:%S')
                if pattern.get('last_occurrence'):
                    pattern['last_occurrence'] = pattern['last_occurrence'].strftime('%Y-%m-%d %H:%M:%S')
                if pattern.get('avg_resolution_minutes'):
                    pattern['avg_resolution_hours'] = round(pattern['avg_resolution_minutes'] / 60, 2)
            
            return {
                "recurring_patterns": recurring_patterns,
                "time_patterns": time_patterns,
                "correlation_patterns": correlation_patterns,
                "analysis_period_days": days
            }
            
    except Exception as e:
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()


@bp.post("/trigger-recovery-check")
@log_api_call("alerts_extended.trigger_recovery_check")
def trigger_recovery_check():
    """触发告警恢复检查"""
    current_app.logger.info("Triggering alert recovery check")
    # 这个API可以用来手动触发告警恢复检查
    # 实际实现中可以调用现有的 alert_recovery_checker.py 脚本
    
    try:
        # 这里可以集成现有的恢复检查逻辑
        # 或者通过系统调用执行恢复检查脚本
        
        return {
            "message": "Recovery check triggered successfully",
            "status": "initiated",
            "timestamp": datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
            "note": "恢复检查已启动，结果将在后台处理"
        }
        
    except Exception as e:
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500


@bp.get("/metrics")
@log_api_call("alerts_extended.get_alert_metrics")
def get_alert_metrics():
    """获取告警指标"""
    current_app.logger.info("Getting alert metrics")
    time_range = request.args.get("time_range", "24h")  # 1h, 24h, 7d, 30d
    
    # 时间范围映射
    time_mapping = {
        "1h": ("INTERVAL 1 HOUR", 1),
        "24h": ("INTERVAL 24 HOUR", 24), 
        "7d": ("INTERVAL 7 DAY", 7 * 24),
        "30d": ("INTERVAL 30 DAY", 30 * 24)
    }
    
    if time_range not in time_mapping:
        return {"success": False, "error": "Invalid time_range", "code": "BAD_REQUEST"}, 400
    
    interval_str, hours = time_mapping[time_range]
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            # 基础指标
            cur.execute(f"""
                SELECT 
                    COUNT(*) as total_alerts,
                    COUNT(*) / %s as alerts_per_hour,
                    SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) as active_alerts,
                    SUM(CASE WHEN status = 'RESOLVED' THEN 1 ELSE 0 END) as resolved_alerts,
                    AVG(CASE WHEN resolved_time IS NOT NULL 
                        THEN TIMESTAMPDIFF(MINUTE, sent_time, resolved_time) 
                        ELSE NULL END) as avg_resolution_minutes,
                    COUNT(DISTINCT job_name) as affected_jobs
                FROM flink_cluster_alert_history 
                WHERE sent_time >= DATE_SUB(NOW(), {interval_str})
            """, (hours,))
            
            metrics = cur.fetchone()
            
            # MTTR (Mean Time To Resolution) 计算
            mttr_hours = round(metrics['avg_resolution_minutes'] / 60, 2) if metrics['avg_resolution_minutes'] else 0
            
            # 告警频率趋势
            cur.execute(f"""
                SELECT 
                    DATE_FORMAT(sent_time, '%H:00') as time_slot,
                    COUNT(*) as count
                FROM flink_cluster_alert_history 
                WHERE sent_time >= DATE_SUB(NOW(), {interval_str})
                GROUP BY DATE_FORMAT(sent_time, '%H:00')
                ORDER BY time_slot
            """)
            
            frequency_trend = cur.fetchall()
            
            # 严重程度分布
            cur.execute(f"""
                SELECT 
                    alert_type,
                    COUNT(*) as count,
                    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM flink_cluster_alert_history 
                                             WHERE sent_time >= DATE_SUB(NOW(), {interval_str})), 2) as percentage
                FROM flink_cluster_alert_history 
                WHERE sent_time >= DATE_SUB(NOW(), {interval_str})
                GROUP BY alert_type
            """)
            
            severity_distribution = cur.fetchall()
            
            return {
                "time_range": time_range,
                "metrics": {
                    **metrics,
                    "mttr_hours": mttr_hours,
                    "resolution_rate": round(
                        (metrics['resolved_alerts'] / metrics['total_alerts']) * 100, 2
                    ) if metrics['total_alerts'] > 0 else 0
                },
                "frequency_trend": frequency_trend,
                "severity_distribution": severity_distribution,
                "generated_at": datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            }
            
    except Exception as e:
        return {"success": False, "error": str(e), "code": "SERVER_ERROR"}, 500
    finally:
        conn.close()
